﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using fuzhu;
using XXX.Models.XXXEntities;
using XXX.Models;
using XXX.Common;

namespace XXX.Bo
{
   public  partial class U8API
    {

        public string InsertInventory(LK_Inventory inventory, string ConStr)
        {
            using (SqlConnection connection = new SqlConnection(ConStr))
            {
                connection.Open();
                SqlTransaction tran = connection.BeginTransaction();
                try
                {
                    string sql = string.Format(@"select count(*) from inventory where cinvcode = '{0}'", inventory.InvCode);
                    int iCnt = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans(sql, tran));
                    if (iCnt > 0)
                    {
                        throw new Exception("货号已存在");
                    }
                    sql = string.Format(@"delete  from inventory_sub where cinvsubcode = '{0}'   
delete  from bas_part where invcode =  '{0}' 
delete  from inventory_extradefine where cinvcode = '{0}' 
", inventory.InvCode);
                    DbHelper2.ExecuteSqlWithTrans(sql, tran);




                    int iPartid = DbHelper.GetDbInt(DbHelper2.GetSingleWithTrans("select max(partid)+1 from bas_part", tran));
                    #region 插入bas_part
                    string sQLString = @" INSERT INTO [bas_part]
           ([PartId]
           ,[InvCode]
           ,[Free1]
           ,[Free2]
           ,[Free3]
           ,[Free4]
           ,[Free5]
           ,[Free6]
           ,[Free7]
           ,[Free8]
           ,[Free9]
           ,[Free10]
           ,[SafeQty]
           ,[MinQty]
           ,[MulQty]
           ,[FixQty]
           ,[bVirtual]
           ,[DrawCode]
           ,[LLC]
           ,[cBasEngineerFigNo]
           ,[fBasMaxSupply]
           ,[iSurenessType]
           ,[iDateType]
           ,[iDateSum]
           ,[iDynamicSurenessType]
           ,[iBestrowSum]
           ,[iPercentumSum]
           ,[RoundingFactor]
           ,[FreeStockFlag])
  select  @partid
           ,@cinvcode
           ,[Free1]
           ,[Free2]
           ,[Free3]
           ,[Free4]
           ,[Free5]
           ,[Free6]
           ,[Free7]
           ,[Free8]
           ,[Free9]
           ,[Free10]
           ,[SafeQty]
           ,[MinQty]
           ,[MulQty]
           ,[FixQty]
           ,[bVirtual]
           ,[DrawCode]
           ,[LLC]
           ,[cBasEngineerFigNo]
           ,[fBasMaxSupply]
           ,[iSurenessType]
           ,[iDateType]
           ,[iDateSum]
           ,[iDynamicSurenessType]
           ,[iBestrowSum]
           ,[iPercentumSum]
           ,[RoundingFactor]
           ,[FreeStockFlag]  from bas_part where invcode = @Model ";


                    DbHelper2.ExecuteSqlWithTrans(sQLString, new SqlParameter[]{
                       new SqlParameter("@cinvcode",inventory.InvCode),
                       new SqlParameter("@partid",iPartid),
                       new SqlParameter("@Model",CanShu.cModel),}, CommandType.Text, tran);
                    #endregion

                    #region 查询存货辅助表
                    sQLString = @"INSERT INTO [Inventory_Sub]
           ([cInvSubCode]
           ,[fBuyExcess]
           ,[iSurenessType]
           ,[iDateType]
           ,[iDateSum]
           ,[iDynamicSurenessType]
           ,[iBestrowSum]
           ,[iPercentumSum]
           ,[bIsAttachFile]
           ,[bInByProCheck]
           ,[iRequireTrackStyle]
           ,[iExpiratDateCalcu]
           ,[iBOMExpandUnitType]
           ,[bPurPriceFree1]
           ,[bPurPriceFree2]
           ,[bPurPriceFree3]
           ,[bPurPriceFree4]
           ,[bPurPriceFree5]
           ,[bPurPriceFree6]
           ,[bPurPriceFree7]
           ,[bPurPriceFree8]
           ,[bPurPriceFree9]
           ,[bPurPriceFree10]
           ,[bOMPriceFree1]
           ,[bOMPriceFree2]
           ,[bOMPriceFree3]
           ,[bOMPriceFree4]
           ,[bOMPriceFree5]
           ,[bOMPriceFree6]
           ,[bOMPriceFree7]
           ,[bOMPriceFree8]
           ,[bOMPriceFree9]
           ,[bOMPriceFree10]
           ,[bSalePriceFree1]
           ,[bSalePriceFree2]
           ,[bSalePriceFree3]
           ,[bSalePriceFree4]
           ,[bSalePriceFree5]
           ,[bSalePriceFree6]
           ,[bSalePriceFree7]
           ,[bSalePriceFree8]
           ,[bSalePriceFree9]
           ,[bSalePriceFree10]
           ,[fInvOutUpLimit]
           ,[bBondedInv]
           ,[bBatchCreate]
           ,[bBatchProperty1]
           ,[bBatchProperty2]
           ,[bBatchProperty3]
           ,[bBatchProperty4]
           ,[bBatchProperty5]
           ,[bBatchProperty6]
           ,[bBatchProperty7]
           ,[bBatchProperty8]
           ,[bBatchProperty9]
           ,[bBatchProperty10]
           ,[bControlFreeRange1]
           ,[bControlFreeRange2]
           ,[bControlFreeRange3]
           ,[bControlFreeRange4]
           ,[bControlFreeRange5]
           ,[bControlFreeRange6]
           ,[bControlFreeRange7]
           ,[bControlFreeRange8]
           ,[bControlFreeRange9]
           ,[bControlFreeRange10]
           ,[fInvCIQExch]
           ,[iWarrantyPeriod]
           ,[iWarrantyUnit]
           ,[bInvKeyPart]
           ,[iAcceptEarlyDays]
           ,[fProcessCost]
           ,[fCurLLaborCost]
           ,[fCurLVarManuCost]
           ,[fCurLFixManuCost]
           ,[fCurLOMCost]
           ,[fNextLLaborCost]
           ,[fNextLVarManuCost]
           ,[fNextLFixManuCost]
           ,[fNextLOMCost]
           ,[dInvCreateDatetime]
           ,[cInvAppDocNo]
           ,[bPUQuota]
           ,[bInvROHS]
           ,[bPrjMat]
           ,[fPrjMatLimit]
           ,[bInvAsset]
           ,[bSrvProduct]
           ,[iAcceptDelayDays]
           ,[iPlanCheckDay]
           ,[iMaterialsCycle]
           ,[iDrawType]
           ,[bSCkeyProjections]
           ,[iSupplyPeriodType]
           ,[iTimeBucketId]
           ,[iAvailabilityDate]
           ,[fMaterialCost]
           ,[bImport]
           ,[iNearRejectDays]
           ,[bCheckSubitemCost]
           ,[fRoundFactor]
           ,[bConsiderFreeStock]
           ,[bSuitRetail])
    select @cinvcode
           ,[fBuyExcess]
           ,[iSurenessType]
           ,[iDateType]
           ,[iDateSum]
           ,[iDynamicSurenessType]
           ,[iBestrowSum]
           ,[iPercentumSum]
           ,[bIsAttachFile]
           ,[bInByProCheck]
           ,[iRequireTrackStyle]
           ,[iExpiratDateCalcu]
           ,[iBOMExpandUnitType]
           ,[bPurPriceFree1]
           ,[bPurPriceFree2]
           ,[bPurPriceFree3]
           ,[bPurPriceFree4]
           ,[bPurPriceFree5]
           ,[bPurPriceFree6]
           ,[bPurPriceFree7]
           ,[bPurPriceFree8]
           ,[bPurPriceFree9]
           ,[bPurPriceFree10]
           ,[bOMPriceFree1]
           ,[bOMPriceFree2]
           ,[bOMPriceFree3]
           ,[bOMPriceFree4]
           ,[bOMPriceFree5]
           ,[bOMPriceFree6]
           ,[bOMPriceFree7]
           ,[bOMPriceFree8]
           ,[bOMPriceFree9]
           ,[bOMPriceFree10]
           ,[bSalePriceFree1]
           ,[bSalePriceFree2]
           ,[bSalePriceFree3]
           ,[bSalePriceFree4]
           ,[bSalePriceFree5]
           ,[bSalePriceFree6]
           ,[bSalePriceFree7]
           ,[bSalePriceFree8]
           ,[bSalePriceFree9]
           ,[bSalePriceFree10]
           ,[fInvOutUpLimit]
           ,[bBondedInv]
           ,[bBatchCreate]
           ,[bBatchProperty1]
           ,[bBatchProperty2]
           ,[bBatchProperty3]
           ,[bBatchProperty4]
           ,[bBatchProperty5]
           ,[bBatchProperty6]
           ,[bBatchProperty7]
           ,[bBatchProperty8]
           ,[bBatchProperty9]
           ,[bBatchProperty10]
           ,[bControlFreeRange1]
           ,[bControlFreeRange2]
           ,[bControlFreeRange3]
           ,[bControlFreeRange4]
           ,[bControlFreeRange5]
           ,[bControlFreeRange6]
           ,[bControlFreeRange7]
           ,[bControlFreeRange8]
           ,[bControlFreeRange9]
           ,[bControlFreeRange10]
           ,[fInvCIQExch]
           ,[iWarrantyPeriod]
           ,[iWarrantyUnit]
           ,[bInvKeyPart]
           ,[iAcceptEarlyDays]
           ,[fProcessCost]
           ,[fCurLLaborCost]
           ,[fCurLVarManuCost]
           ,[fCurLFixManuCost]
           ,[fCurLOMCost]
           ,[fNextLLaborCost]
           ,[fNextLVarManuCost]
           ,[fNextLFixManuCost]
           ,[fNextLOMCost]
           ,getdate()
           ,[cInvAppDocNo]
           ,[bPUQuota]
           ,[bInvROHS]
           ,[bPrjMat]
           ,[fPrjMatLimit]
           ,[bInvAsset]
           ,[bSrvProduct]
           ,[iAcceptDelayDays]
           ,[iPlanCheckDay]
           ,[iMaterialsCycle]
           ,[iDrawType]
           ,[bSCkeyProjections]
           ,[iSupplyPeriodType]
           ,[iTimeBucketId]
           ,[iAvailabilityDate]
           ,[fMaterialCost]
           ,[bImport]
           ,[iNearRejectDays]
           ,[bCheckSubitemCost]
           ,[fRoundFactor]
           ,[bConsiderFreeStock]
           ,[bSuitRetail] from  [Inventory_Sub] where cInvSubCode = @Model";
                    DbHelper2.ExecuteSqlWithTrans(sQLString, new SqlParameter[]{
                       new SqlParameter("@cinvcode",inventory.InvCode),
                       new SqlParameter("@Model",CanShu.cModel)}, CommandType.Text, tran);
                    #endregion

                    #region 查询存货档案
                    sQLString = @"INSERT INTO [Inventory]
           ([cInvCode]
           ,[cInvAddCode]
           ,[cInvName]
           ,[cInvStd]
           ,[cInvCCode]
           ,[cVenCode]
           ,[cReplaceItem]
           ,[cPosition]
           ,[bSale]
           ,[bPurchase]
           ,[bSelf]
           ,[bComsume]
           ,[bProducing]
           ,[bService]
           ,[bAccessary]
           ,[iTaxRate]
           ,[iInvWeight]
           ,[iVolume]
           ,[iInvRCost]
           ,[iInvSPrice]
           ,[iInvSCost]
           ,[iInvLSCost]
           ,[iInvNCost]
           ,[iInvAdvance]
           ,[iInvBatch]
           ,[iSafeNum]
           ,[iTopSum]
           ,[iLowSum]
           ,[iOverStock]
           ,[cInvABC]
           ,[bInvQuality]
           ,[bInvBatch]
           ,[bInvEntrust]
           ,[bInvOverStock]
           ,[dSDate]
           ,[dEDate]
           ,[bFree1]
           ,[bFree2]
           ,[cInvDefine1]
           ,[cInvDefine2]
           ,[cInvDefine3]
           ,[bInvType]
           ,[iInvMPCost]
           ,[cQuality]
           ,[iInvSaleCost]
           ,[iInvSCost1]
           ,[iInvSCost2]
           ,[iInvSCost3]
           ,[bFree3]
           ,[bFree4]
           ,[bFree5]
           ,[bFree6]
           ,[bFree7]
           ,[bFree8]
           ,[bFree9]
           ,[bFree10]
           ,[cCreatePerson]
           ,[cModifyPerson]
           ,[dModifyDate]
           ,[fSubscribePoint]
           ,[fVagQuantity]
           ,[cValueType]
           ,[bFixExch]
           ,[fOutExcess]
           ,[fInExcess]
           ,[iMassDate]
           ,[iWarnDays]
           ,[fExpensesExch]
           ,[bTrack]
           ,[bSerial]
           ,[bBarCode]
           ,[iId]
           ,[cBarCode]
           ,[cInvDefine4]
           ,[cInvDefine5]
           ,[cInvDefine6]
           ,[cInvDefine7]
           ,[cInvDefine8]
           ,[cInvDefine9]
           ,[cInvDefine10]
           ,[cInvDefine11]
           ,[cInvDefine12]
           ,[cInvDefine13]
           ,[cInvDefine14]
           ,[cInvDefine15]
           ,[cInvDefine16]
           ,[iGroupType]
           ,[cGroupCode]
           ,cComUnitCode
           ,[cAssComUnitCode]
           ,[cSAComUnitCode]
           ,[cPUComUnitCode]
           ,[cSTComUnitCode]
           ,[cCAComUnitCode]
           ,[cFrequency]
           ,[iFrequency]
           ,[iDays]
           ,[dLastDate]
           ,[iWastage]
           ,[bSolitude]
           ,[cEnterprise]
           ,[cAddress]
           ,[cFile]
           ,[cLabel]
           ,[cCheckOut]
           ,[cLicence]
           ,[bSpecialties]
           ,[cDefWareHouse]
           ,[iHighPrice]
           ,[iExpSaleRate]
           ,[cPriceGroup]
           ,[cOfferGrade]
           ,[iOfferRate]
           ,[cMonth]
           ,[iAdvanceDate]
           ,[cCurrencyName]
           ,[cProduceAddress]
           ,[cProduceNation]
           ,[cRegisterNo]
           ,[cEnterNo]
           ,[cPackingType]
           ,[cEnglishName]
           ,[bPropertyCheck]
           ,[cPreparationType]
           ,[cCommodity]
           ,[iRecipeBatch]
           ,[cNotPatentName]
           ,[bPromotSales]
           ,[iPlanPolicy]
           ,[iROPMethod]
           ,[iBatchRule]
           ,[fBatchIncrement]
           ,[iAssureProvideDays]
           ,[iTestStyle]
           ,[iDTMethod]
           ,[fDTRate]
           ,[fDTNum]
           ,[cDTUnit]
           ,[iDTStyle]
           ,[iQTMethod]
           ,[PictureGUID]
           ,[bPlanInv]
           ,[bProxyForeign]
           ,[bATOModel]
           ,[bCheckItem]
           ,[bPTOModel]
           ,[bEquipment]
           ,[cProductUnit]
           ,[fOrderUpLimit]
           ,[cMassUnit]
           ,[fRetailPrice]
           ,[cInvDepCode]
           ,[iAlterAdvance]
           ,[fAlterBaseNum]
           ,[cPlanMethod]
           ,[bMPS]
           ,[bROP]
           ,[bRePlan]
           ,[cSRPolicy]
           ,[bBillUnite]
           ,[iSupplyDay]
           ,[fSupplyMulti]
           ,[fMinSupply]
           ,[bCutMantissa]
           ,[cInvPersonCode]
           ,[iInvTfId]
           ,[cEngineerFigNo]
           ,[bInTotalCost]
           ,[iSupplyType]
           ,[bConfigFree1]
           ,[bConfigFree2]
           ,[bConfigFree3]
           ,[bConfigFree4]
           ,[bConfigFree5]
           ,[bConfigFree6]
           ,[bConfigFree7]
           ,[bConfigFree8]
           ,[bConfigFree9]
           ,[bConfigFree10]
           ,[iDTLevel]
           ,[cDTAQL]
           ,[bPeriodDT]
           ,[cDTPeriod]
           ,[iBigMonth]
           ,[iBigDay]
           ,[iSmallMonth]
           ,[iSmallDay]
           ,[bOutInvDT]
           ,[bBackInvDT]
           ,[iEndDTStyle]
           ,[bDTWarnInv]
           ,[fBackTaxRate]
           ,[cCIQCode]
           ,[cWGroupCode]
           ,[cWUnit]
           ,[fGrossW]
           ,[cVGroupCode]
           ,[cVUnit]
           ,[fLength]
           ,[fWidth]
           ,[fHeight]
           ,[iDTUCounter]
           ,[iDTDCounter]
           ,[iBatchCounter]
           ,[cShopUnit]
           ,[cPurPersonCode]
           ,[bImportMedicine]
           ,[bFirstBusiMedicine]
           ,[bForeExpland]
           ,[cInvPlanCode]
           ,[fConvertRate]
           ,[dReplaceDate]
           ,[bInvModel]
           ,[bKCCutMantissa]
           ,[bReceiptByDT]
           ,[iImpTaxRate]
           ,[iExpTaxRate]
           ,[bExpSale]
           ,[iDrawBatch]
           ,[bCheckBSATP]
           ,[cInvProjectCode]
           ,[iTestRule]
           ,[cRuleCode]
           ,[bCheckFree1]
           ,[bCheckFree2]
           ,[bCheckFree3]
           ,[bCheckFree4]
           ,[bCheckFree5]
           ,[bCheckFree6]
           ,[bCheckFree7]
           ,[bCheckFree8]
           ,[bCheckFree9]
           ,[bCheckFree10]
           ,[bBomMain]
           ,[bBomSub]
           ,[bProductBill]
           ,[iCheckATP]
           ,[iInvATPId]
           ,[iPlanTfDay]
           ,[iOverlapDay]
           ,[bPiece]
           ,[bSrvItem]
           ,[bSrvFittings]
           ,[fMaxSupply]
           ,[fMinSplit]
           ,[bSpecialOrder]
           ,[bTrackSaleBill]
           ,[cInvMnemCode]
           ,[iPlanDefault]
           ,[iPFBatchQty]
           ,[iAllocatePrintDgt]
           ,[bCheckBatch]
           ,[bMngOldpart]
           ,[iOldpartMngRule])
    select @cinvcode
           ,@cInvAddCode
           ,@cinvname
           ,null
           ,cinvccode
           ,[cVenCode]
           ,[cReplaceItem]
           ,[cPosition]
           ,[bSale]
           ,[bPurchase]
           ,[bSelf]
           ,[bComsume]
           ,[bProducing]
           ,[bService]
           ,[bAccessary]
           ,[iTaxRate]
           ,[iInvWeight]
           ,[iVolume]
           ,[iInvRCost]
           ,[iInvSPrice]
           ,[iInvSCost]
           ,[iInvLSCost]
           ,[iInvNCost]
           ,[iInvAdvance]
           ,[iInvBatch]
           ,[iSafeNum]
           ,[iTopSum]
           ,[iLowSum]
           ,[iOverStock]
           ,[cInvABC]
           ,[bInvQuality]
           ,[bInvBatch]
           ,[bInvEntrust]
           ,[bInvOverStock]
           ,convert(varchar(20),getdate(),23)
           ,[dEDate]
           ,[bFree1]
           ,[bFree2]
           ,null
           ,null
           ,null
           ,[bInvType]
           ,[iInvMPCost]
           ,[cQuality]
           ,[iInvSaleCost]
           ,[iInvSCost1]
           ,[iInvSCost2]
           ,[iInvSCost3]
           ,[bFree3]
           ,[bFree4]
           ,[bFree5]
           ,[bFree6]
           ,[bFree7]
           ,[bFree8]
           ,[bFree9]
           ,[bFree10]
           ,'chemenu'
           ,null
           ,null
           ,[fSubscribePoint]
           ,[fVagQuantity]
           ,[cValueType]
           ,[bFixExch]
           ,[fOutExcess]
           ,[fInExcess]
           ,[iMassDate]
           ,[iWarnDays]
           ,[fExpensesExch]
           ,[bTrack]
           ,[bSerial]
           ,[bBarCode]
           ,[iId]
           ,[cBarCode]
           ,null
           ,null
           ,null
           ,null
           ,null
           ,null
           ,null
           ,null
           ,null
           ,null
           ,null
           ,null
           ,null
           ,[iGroupType]
           ,[cGroupCode]
           ,cComUnitCode
           ,[cAssComUnitCode]
           ,[cSAComUnitCode]
           ,[cPUComUnitCode]
           ,[cSTComUnitCode]
           ,[cCAComUnitCode]
           ,[cFrequency]
           ,[iFrequency]
           ,[iDays]
           ,[dLastDate]
           ,[iWastage]
           ,[bSolitude]
           ,[cEnterprise]
           ,[cAddress]
           ,[cFile]
           ,[cLabel]
           ,[cCheckOut]
           ,[cLicence]
           ,[bSpecialties]
           ,[cDefWareHouse]
           ,[iHighPrice]
           ,[iExpSaleRate]
           ,[cPriceGroup]
           ,[cOfferGrade]
           ,[iOfferRate]
           ,[cMonth]
           ,[iAdvanceDate]
           ,[cCurrencyName]
           ,[cProduceAddress]
           ,[cProduceNation]
           ,[cRegisterNo]
           ,[cEnterNo]
           ,[cPackingType]
           ,@cEnglishName
           ,[bPropertyCheck]
           ,[cPreparationType]
           ,[cCommodity]
           ,[iRecipeBatch]
           ,[cNotPatentName]
           ,[bPromotSales]
           ,[iPlanPolicy]
           ,[iROPMethod]
           ,[iBatchRule]
           ,[fBatchIncrement]
           ,[iAssureProvideDays]
           ,[iTestStyle]
           ,[iDTMethod]
           ,[fDTRate]
           ,[fDTNum]
           ,[cDTUnit]
           ,[iDTStyle]
           ,[iQTMethod]
           ,null
           ,[bPlanInv]
           ,[bProxyForeign]
           ,[bATOModel]
           ,[bCheckItem]
           ,[bPTOModel]
           ,[bEquipment]
           ,[cProductUnit]
           ,[fOrderUpLimit]
           ,[cMassUnit]
           ,[fRetailPrice]
           ,[cInvDepCode]
           ,[iAlterAdvance]
           ,[fAlterBaseNum]
           ,[cPlanMethod]
           ,[bMPS]
           ,[bROP]
           ,[bRePlan]
           ,[cSRPolicy]
           ,[bBillUnite]
           ,[iSupplyDay]
           ,[fSupplyMulti]
           ,[fMinSupply]
           ,[bCutMantissa]
           ,[cInvPersonCode]
           ,[iInvTfId]
           ,null
           ,[bInTotalCost]
           ,[iSupplyType]
           ,[bConfigFree1]
           ,[bConfigFree2]
           ,[bConfigFree3]
           ,[bConfigFree4]
           ,[bConfigFree5]
           ,[bConfigFree6]
           ,[bConfigFree7]
           ,[bConfigFree8]
           ,[bConfigFree9]
           ,[bConfigFree10]
           ,[iDTLevel]
           ,[cDTAQL]
           ,[bPeriodDT]
           ,[cDTPeriod]
           ,[iBigMonth]
           ,[iBigDay]
           ,[iSmallMonth]
           ,[iSmallDay]
           ,[bOutInvDT]
           ,[bBackInvDT]
           ,[iEndDTStyle]
           ,[bDTWarnInv]
           ,[fBackTaxRate]
           ,[cCIQCode]
           ,[cWGroupCode]
           ,[cWUnit]
           ,[fGrossW]
           ,[cVGroupCode]
           ,[cVUnit]
           ,[fLength]
           ,[fWidth]
           ,[fHeight]
           ,[iDTUCounter]
           ,[iDTDCounter]
           ,[iBatchCounter]
           ,[cShopUnit]
           ,[cPurPersonCode]
           ,[bImportMedicine]
           ,[bFirstBusiMedicine]
           ,[bForeExpland]
           ,[cInvPlanCode]
           ,[fConvertRate]
           ,[dReplaceDate]
           ,[bInvModel]
           ,[bKCCutMantissa]
           ,[bReceiptByDT]
           ,[iImpTaxRate]
           ,[iExpTaxRate]
           ,[bExpSale]
           ,[iDrawBatch]
           ,[bCheckBSATP]
           ,[cInvProjectCode]
           ,[iTestRule]
           ,[cRuleCode]
           ,[bCheckFree1]
           ,[bCheckFree2]
           ,[bCheckFree3]
           ,[bCheckFree4]
           ,[bCheckFree5]
           ,[bCheckFree6]
           ,[bCheckFree7]
           ,[bCheckFree8]
           ,[bCheckFree9]
           ,[bCheckFree10]
           ,[bBomMain]
           ,[bBomSub]
           ,[bProductBill]
           ,[iCheckATP]
           ,[iInvATPId]
           ,[iPlanTfDay]
           ,[iOverlapDay]
           ,[bPiece]
           ,[bSrvItem]
           ,[bSrvFittings]
           ,[fMaxSupply]
           ,[fMinSplit]
           ,[bSpecialOrder]
           ,[bTrackSaleBill]
           ,[cInvMnemCode]
           ,[iPlanDefault]
           ,[iPFBatchQty]
           ,[iAllocatePrintDgt]
           ,[bCheckBatch]
           ,[bMngOldpart]
           ,[iOldpartMngRule] from inventory where cInvCode = @Model";

                    DbHelper2.ExecuteSqlWithTrans(sQLString, new SqlParameter[]{
                       new SqlParameter("@cinvcode",inventory.InvCode),
                       new SqlParameter("@cinvname",inventory.InvName),
                       new SqlParameter("@cEnglishName",inventory.InvEnglishName),
                       new SqlParameter("@cInvAddCode",inventory.InvAddCode),
                       new SqlParameter("@Model",CanShu.cModel)}, CommandType.Text, tran);
                    #endregion

                    sQLString = @"INSERT INTO[dbo].[Inventory_extradefine]
                ([cInvCode]
          ,[cidefine1])
     values(@cinvcode,@cidefine1)";
                    DbHelper2.ExecuteSqlWithTrans(sQLString, new SqlParameter[]{
                       new SqlParameter("@cinvcode",inventory.InvCode),
                         new SqlParameter("@cidefine1",inventory.InvEnglishName)}, CommandType.Text, tran);

                    tran.Commit();
                    //connection.Close();
                    //DbHelper.CommitTransAndCloseConnection(tran);
                    return "ok";

                }
                catch (Exception exception)
                {
                    tran.Rollback();
                    //connection.Close();
                    return ("导入失败：" + exception.Message);
                }

                //return "导入完成！";


            }
        }

        /// <summary>
        /// 插入客户档案，返回客户编码或者错误信息
        /// </summary>
        /// <param name="customer"></param>
        /// <param name="constr"></param>
        /// <param name="cError">错误信息</param>
        /// <param name="cCusCode">客户编码</param>
        /// <returns></returns>
        public RetModelbool InsertCustomer(Customer customer)
        {
            string constr = CanShu.conStr;
            RetModelbool ret = new RetModelbool();

            //检查是否输入
            string cError = "";
            string cpersonname = "";
            //string  cCusCode = "";
            using (SqlConnection connection = new SqlConnection(constr))
            {
                connection.Open();
                SqlTransaction tran = connection.BeginTransaction();
                try
                {

                    string sql = string.Format(@"select ccuscode from customer where ccusname = '{0}' or cCusRegCode='{1}' ",
                        customer.cCusName, customer.cCusregcode);
                    DataTable dtcus = DbHelper2.QueryWithTrans(sql, tran).Tables[0];
                    if (dtcus.Rows.Count > 0)
                    {
                        string ccuscode = DbHelper.GetDbString(dtcus.Rows[0]["ccuscode"]);
                        ret.code = 200;
                        ret.message = ccuscode;
                        return ret;
                    }
                    //取客户分类
                    if (!string.IsNullOrEmpty(customer.cCCode) && customer.cCCode != "")
                    {
                        sql = string.Format(@"SELECT cCCCode FROM dbo.CustomerClass WHERE cCCName = '{0}' ",
                            customer.cCCode);
                        DataTable dtccus = DbHelper2.QueryWithTrans(sql, tran).Tables[0];
                        if (dtccus.Rows.Count > 0)
                        {
                            customer.cCCode = DbHelper.GetDbString(dtccus.Rows[0]["cCCCode"]);
                        }
                        else
                        {
                            cError += "客户分类cCCode不正确;";
                        }
                    }
                    else
                    {
                        cError += "客户分类cCCode不能为空;";
                    }
                    //取业务员
                    if (!string.IsNullOrEmpty(customer.cCusPPerson) && customer.cCusPPerson != "")
                    {
                        string cpersoncode = customer.cCusPPerson;
                        cpersonname = customer.cCusPPerson;
                        if (customer.cCusPPerson.Contains("|"))
                        {
                            string[] str = customer.cCusPPerson.Split("|");
                            cpersoncode = str[0];
                            cpersonname = str[1];
                        }
                        sql = string.Format(@"SELECT cdepcode,cpersonname FROM person  WHERE cpersoncode = '{0}' ", cpersoncode);
                        DataTable dtperson = DbHelper2.QueryWithTrans(sql, tran).Tables[0];
                        if (dtperson.Rows.Count > 0)
                        {
                            if (cpersonname != DbHelper.GetDbString(dtperson.Rows[0]["cpersonname"]))
                            {
                                cError += "业务员编码和姓名不匹配";
                                
                            }
                            customer.cCusDepart = DbHelper.GetDbString(dtperson.Rows[0]["cdepcode"]);
                            customer.cCusPPerson = cpersoncode;

                        }
                        else
                        {
                            cError += "业务员编码不存在";
                        }
                    }
                    else
                    {
                        cError += "业务员编码不能为空";
                    }
                    if (!string.IsNullOrEmpty(cError) && cError != "")
                    {
                        throw new Exception(cError);
                    }

                    //写权限
                    //加数据权限,如果不是账套主管，加控制
                    //sql = string.Format(@"select count(1)  from ufsystem..ua_role  where cGroup_Id = 'DATA-MANAGER' and cUser_Id ='{0}'", customer.cCusPPerson);
                    //    string cRole = DbHelper.GetDbString(DbHelper2.GetSingleWithTrans(sql, tran));
                    //    string cAccid = "";
                    //    if (cRole == "0")
                    //    {
                            sql = string.Format(@"select cadcode from  AA_AuthDimen_Sub where  cadname='{0}'", cpersonname);
                            string cAccid = DbHelper.GetDbString(DbHelper2.GetSingleWithTrans(sql, tran));
                            if (cAccid == "")
                            {
                            throw new Exception("请联系管理员建立该业务员的权限维度"); 

                            }
                        //}
                     

                   
                        //获得编码
                        int iMax = Convert.ToInt32(DbHelper2.ExecuteScalar("select cNumber from VoucherHistory where CardNumber = 'Customer' and cSeed = '" + customer.cCCode + "'", constr));
                    customer.cCusCode = customer.cCCode + (iMax + 1).ToString().PadLeft(3, '0');
                    DbHelper2.ExecuteSqlWithTrans("update VoucherHistory set cNumber='" + (iMax + 1).ToString() + "' where CardNumber = 'Customer' and cSeed = '" + customer.cCCode + "'", tran);

                    sql = @"insert into Customer(ccuscode,cCusName,cCusAbbName,ccccode,cCusAddress,cCusregcode,dCusDevDate,cCusPerson,cCusPhone,cCusHand,cCusPPerson,
iCusDisRate,iCusCreLine,iCusCreDate,cCusHeadCode,cCusDepart,iFrequency,iCostGrade,cCreatePerson,iId,cInvoiceCompany,bCredit,bCreditDate,bCreditByHead,bLicenceDate,bBusinessDate,
bProxy,bLimitSale,bHomeBranch,bCusState,bShop,cCusExch_name,iCusGSPType,bCusDomestic,bCusOverseas,cCusCreditCompany,dCusCreateDatetime,bServiceAttribute,bRequestSign,bOnGPinStore,cCusMngTypeCode,account_type,cCusEmail,
cCusAccount)
values(@ccuscode,@cCusName,@cCusAbbName,@ccccode,@cCusAddress,@cCusregcode,@dCusDevDate,@cCusPerson,@cCusPhone,@cCusHand,@cCusPPerson,
0,0,0,@cCusHeadCode,@cCusDepart,0,-1,@cCreatePerson,@iId,@cInvoiceCompany,0,0,0,0,0,
0,0,0,1,0,@cCusExch_name,0,1,0,@cCusCreditCompany,GETDATE(),0,0,0,@cCusMngTypeCode,26,@cCusEmail,@cCusAccount)";

                    DbHelper2.ExecuteSqlWithTrans(sql, new SqlParameter[]{
                             new SqlParameter("@ccuscode",customer.cCusCode),
                             new SqlParameter("@cCusName",customer.cCusName),
                             new SqlParameter("@cCusAbbName", customer.cCusName),
                             new SqlParameter("@ccccode", customer.cCCode),
                             new SqlParameter("@cCusAddress",DbHelper.ToDbValue(customer.cCusAddress)),
                             new SqlParameter("@cCusregcode",DbHelper.ToDbValue(customer.cCusregcode)),
                             new SqlParameter("@dCusDevDate", customer.dCusCreateDatetime.ToString("yyyy-MM-dd")),
                             new SqlParameter("@cCusPerson", DbHelper.ToDbValue(customer.cCusPerson)),
                             new SqlParameter("@cCusPhone", DbHelper.ToDbValue(customer.cCusPhone)),
                             new SqlParameter("@cCusHand",  DbHelper.ToDbValue(customer.cCusHand)),
                             new SqlParameter("@cCusPPerson",customer.cCusPPerson),
                             new SqlParameter("@cCusHeadCode",customer.cCusCode),
                             new SqlParameter("@cCusDepart",customer.cCusDepart),
                             new SqlParameter("@cCreatePerson",customer.cCreatePerson),
                             new SqlParameter("@iId",customer.cCusCode)                             ,
                             new SqlParameter("@cInvoiceCompany",customer.cCusCode)                             ,
                             new SqlParameter("@cCusBank",DbHelper.ToDbValue(customer.cBank))                             ,
                             new SqlParameter("@cCusCreditCompany",customer.cCusCode)                             ,
                             new SqlParameter("@cCusMngTypeCode","999"),
                              new SqlParameter("@cCusExch_name", DBService.gnvl(customer.cCusExch_name,"人民币")),
                              new SqlParameter("@cCusEmail",DbHelper.ToDbValue(customer.cCusEmail)) ,
                              new SqlParameter("@cCusAccount",DbHelper.ToDbValue(customer.cCusAccount))

                            }, tran);

                    if (!string.IsNullOrEmpty(cAccid) && cAccid != "")
                    {
                        sql = @"insert into customer_Auth(Account_ID,Privilege_Type,Privilege_ID) values (@Account_ID,5,@Privilege_ID)";
                        DbHelper2.ExecuteSqlWithTrans(sql, new SqlParameter[]{
                             new SqlParameter("@Account_ID", customer.cCusCode),
                             new SqlParameter("@Privilege_ID",cAccid)
                                 }, tran);
                    }

                    //联系人不为空,写联系人
                    if (!string.IsNullOrEmpty(customer.cCusPerson) && customer.cCusPerson != "")
                    {
                        sql = string.Format(@"
sELECT  max(cContactCode)*1+1 FROM Crm_Contact WHERE  LEN(cContactCode)=8 and LEFT(cContactCode,1) in (N'0',N'1',N'2',N'3',N'4',N'5',N'6',N'7',N'8',N'9') ");
                        string newcode = DbHelper.GetDbString(DbHelper2.GetSingleWithTrans(sql, tran)).PadLeft(8,'0');
                        string newid = "{" + Guid.NewGuid() + "}";
                        sql = string.Format(@"INSERT INTO crm_contact_extradefine(OID, cContactCode)
        VALUES('{0}','{1}')", newid, newcode);
                        DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        sql = string.Format(@"insert into crm_contact_Auth(Contact_Id, privilege_type, privilege_id)
        VALUES('{0}',0,'{1}')", newcode, customer.cCusDepart);
                        DbHelper2.ExecuteSqlWithTrans(sql, tran);
                        sql = string.Format(@"INSERT INTO Crm_Contact(OID, cContactCode, cCusCode, cContactName, cMobilePhone, cPrincipal, cCreator, dCreateDate, cConDepart)
        VALUES(@OID, @cContactCode, @cCusCode, @cContactName, @cMobilePhone, @cPrincipal, @cCreator, @dCreateDate, @cConDepart)");
                      
                        DbHelper2.ExecuteSqlWithTrans(sql, new SqlParameter[]{
                             new SqlParameter("@OID", newid),
                             new SqlParameter("@cContactCode",newcode),
                             new SqlParameter("@cCusCode",customer.cCusCode),
                             new SqlParameter("@cContactName",customer.cCusPerson),
                             new SqlParameter("@cMobilePhone",DbHelper.ToDbValue(customer.cCusPhone)),
                             new SqlParameter("@cPrincipal",customer.cCusPPerson),
                             new SqlParameter("@cCreator",customer.cCreatePerson),
                             new SqlParameter("@dCreateDate",DateTime.Today.ToString("yyyy-MM-dd")),
                             new SqlParameter("@cConDepart",customer.cCusDepart)
                    }, tran);

                        sql = string.Format(@"update customer set ccuscontactcode = N'{0}'  WHERE  ccuscode = N'{1}'", newid, customer.cCusCode);
                        DbHelper2.ExecuteSqlWithTrans(sql, tran);


                    }


                    sql = @"INSERT INTO CusDeliverAdd(ccuscode,caddcode,cdeliveradd,bdefault,ccusaddressguid)
VALUES(@ccuscode,@caddcode,@cdeliveradd,1,NEWID())";
                    DbHelper2.ExecuteSqlWithTrans(sql, new SqlParameter[]{
                             new SqlParameter("@ccuscode", customer.cCusCode),
                             new SqlParameter("@caddcode",customer.cCusCode+"01"),
                             new SqlParameter("@cdeliveradd",customer.cCusAddress)
                    }, tran);

                    sql = @"INSERT INTO CustomerBank(ccuscode, cbranch, caccountnum, caccountname, bdefault)
VALUES(@ccuscode, @cbranch, @caccountnum, @caccountname, 1)";
                    DbHelper2.ExecuteSqlWithTrans(sql, new SqlParameter[]{
                             new SqlParameter("@ccuscode", customer.cCusCode),
                             new SqlParameter("@cbranch",customer.cBank),
                             new SqlParameter("@caccountnum",customer.cCusAccount),
                             new SqlParameter("@caccountname",customer.cCusName)
                    }, tran);


                    tran.Commit();
                    //connection.Close();
                    //DbHelper.CommitTransAndCloseConnection(tran);
                    ret.code = 200;
                    ret.message =customer.cCusCode;
                    return ret;

                }
                catch (Exception exception)
                {
                    tran.Rollback(); 
                    ret.code = 500;
                    ret.message = exception.Message;
                    return ret;
                }


                //insert into Customer_Auth(account_id,privilege_type,privilege_id)          values (N'00000013',N'5',N'11 ') 

            }
        }


        /// <summary>
        /// 插入客户档案，返回客户编码或者错误信息
        /// </summary>
        /// <param name="customer"></param>
        /// <param name="constr"></param>
        /// <param name="cError">错误信息</param>
        /// <param name="cCusCode">客户编码</param>
        /// <returns></returns>
        public RetModel GetCustomerInfo(CustomerQuery customer)
        {
            string constr = CanShu.conStr;
            RetModel ret = new RetModel();

            try
            {

                string sfilter = "";
                if (!string.IsNullOrEmpty(customer.cCusName))
                {
                    sfilter = string.Format("and a.cCusName = '{0}' ", customer.cCusName);
                }
                if (!string.IsNullOrEmpty(customer.cCusregcode))
                {
                    sfilter = string.Format(" and a.cCusregcode = '{0}' ", customer.cCusregcode);
                }
                if (sfilter == "")
                {
                    return DBService.ReError("请输入客户名称或者税号");
                }    
                string sql = string.Format(@"  SELECT a.cCusCode,a.cCusName,cCusregcode,cCusPPerson,b.cPersonName FROM customer a
LEFT JOIN dbo.Person b ON a.cCusPPerson = b.cPersonCode 
WHERE  1=1  ") + sfilter;
                DataTable dt = DbHelper2.ExecuteTable(sql, constr);

                if (dt.Rows.Count > 0)
                {
                    List<CustomerRet> aiSen001s1 = TableListConverter<CustomerRet>.TableToList2(dt);
                    List<object> aiSen001s = aiSen001s1.ConvertAll(s => (object)s);
                    return DBService.ReOK(aiSen001s);
                }
                else
                {
                    return DBService.ReError("没有客户信息");
                }


            }
            catch (Exception exception)
            {

                ret.code = 500;
                ret.message = exception.Message;
                return ret;
            }


            //insert into Customer_Auth(account_id,privilege_type,privilege_id)          values (N'00000013',N'5',N'11 ') 


        }
    }
}
